"""
Author: Mr.Sun
Datetime: 2024/3/2 8:59
FileName: main.py
Desc: 
"""
import os
import dataset
from sqlalchemy import create_engine
import pandas as pd

# 项目根目录
project_dir = os.path.dirname(os.path.abspath(__file__))
# 数据库地址
db_address = os.path.join(project_dir, 'province.db')

# 原始文件存放目录
raw_data_dir = os.path.join(project_dir, 'data')
# 获取目录下所有的文件
files = [os.path.join(raw_data_dir, file) for file in os.listdir(raw_data_dir)]

# 创建数据库
db = dataset.connect('sqlite:///{}'.format(db_address),
                     engine_kwargs={'connect_args': {'check_same_thread': True}},
                     sqlite_wal_mode=False)

engine = create_engine('sqlite:///{}'.format(db_address), echo=True)


# 将文件写入sqlite
def excel_to_db():
    for file in files:
        df = pd.read_excel(file, sheet_name='Sheet1')

        table_temp = file.split('\\')[-1]
        table_name = table_temp.split('.')[0]

        df.to_sql(table_name, engine, if_exists="replace", index=False)


def posts_sift(table):
    try:
        sql = '''select *
        from (select *
              from {table_name}
              where 学历 in ('本科', '本科及以上','大专及以上')
                and 专业 like '%计算机类%'
              union
              select *
              from {table_name}
              where 学历 in ('本科', '本科及以上','大专及以上')
                and 专业 like '%网络工程%'
              union
              select *
              from {table_name}
              where 学历 in ('本科', '本科及以上','大专及以上')
                and 专业 like '%不限%')'''.format(table_name=table)
        result = [x for x in db.query(sql)]
        return result

    except Exception as e:
        print(e)


if __name__ == '__main__':
    table_list = ['临泉', '太和', '淮南市直', '界首', '省直', '阜南', '阜阳市直', '阜阳颍州', '颍上县', '颍东']
    result = []
    for table in table_list:
        result.extend(posts_sift(table))
    dataframe = pd.DataFrame(result)
    file_name = os.path.join(project_dir, "result.csv")
    dataframe.to_csv(file_name, index=False, sep=',')
